package com.juzhencms.apps.busi.web;

import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.Cache;
import org.springframework.cache.CacheManager;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.alibaba.fastjson.JSON;
import com.juzhencms.apps.base.busi.service.RemoteClientService;
import com.juzhencms.apps.base.common.config.Config;
import com.juzhencms.apps.base.common.database.DBTable;
import com.juzhencms.apps.base.common.util.WebUtil;

import lombok.extern.slf4j.Slf4j;


@Controller
@Slf4j
@RequestMapping("/dbInfo")
public class DBInfoController extends BaseController{
	
	@Autowired
	private RemoteClientService remoteClientService;

	
	@RequestMapping(value="/gridlistData",produces="text/html; charset=UTF-8")
	@ResponseBody
	public String gridlistData(HttpServletRequest request){
		
		//获取当前数据库名称
		String jdbcUrl=Config.getValue("spring.datasource.jdbc-url");
				
		Pattern p=Pattern.compile(":\\d+\\/([^\\?]+)\\?",Pattern.CASE_INSENSITIVE);
		Matcher m=p.matcher(jdbcUrl);	
		m.find();	
		String dbTable=	m.group(1);	
		
		
		Map data=new HashMap();
		List whereParamList=new ArrayList();
		String where="where TABLE_SCHEMA = ? ";
		whereParamList.add(dbTable);
		
		//搜索参数
		if(request.getParameter("table_name")!=null && !request.getParameter("table_name").toString().trim().equals("")){
			where+=" and table_name like ? ";
			whereParamList.add("%"+request.getParameter("table_name")+"%");
		}
		
		//分页处理
		int pageIndex=1;
		if(request.getParameter("pageIndex")!=null && !request.getParameter("pageIndex").toString().trim().equals("")){
			pageIndex=new Integer(request.getParameter("pageIndex")).intValue();
		}
		int pageSize=100;
		if(request.getParameter("pageSize")!=null && !request.getParameter("pageSize").toString().trim().equals("")){
			pageSize=new Integer(request.getParameter("pageSize")).intValue();
		}
		int start=0;
		if(pageIndex>1){
			start=(pageIndex-1)*pageSize;
		}
		whereParamList.add(start);
		whereParamList.add(pageSize);
		
		//排序参数 
		String sortField="table_rows";
		String sortOrder="desc";
		if(request.getParameter("sortField")!=null && !request.getParameter("sortField").equals(""))
			sortField = request.getParameter("sortField");
		if(request.getParameter("sortOrder")!=null && !request.getParameter("sortOrder").equals(""))
			sortOrder = request.getParameter("sortOrder");
		
		String sql="select table_schema,table_name,table_comment,table_rows, " + 
				"truncate(data_length/1024/1024, 2) as data_length, " + 
				"truncate(index_length/1024/1024, 2) as index_length " + 
				"from information_schema.tables " + 
				where + 
				"order by "+sortField+" "+sortOrder+", table_rows desc,data_length desc, index_length desc limit ?,?";
		
		
		//log.info(JSON.toJSONString(whereParamList));
		
		
		//查询列表数据
		List dataList=this.getCommonService().getJdbcDao().querySql(sql, whereParamList.toArray());
		if(dataList!=null && dataList.size()>0){
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			for(int i=0;i<dataList.size();i++){
				Map row=(Map)dataList.get(i);
				if(row.get("create_time")!=null) {
					Date date = (Date)row.get("create_time");
					row.put("create_time",sdf.format(date));
				}
			}
		}
		
		//查询总条数
		Map countRow=this.getCommonService().getJdbcDao().queryRow("select count(*) as count_num,sum(truncate(data_length/1024/1024, 2)) as data_length from information_schema.tables "+where, whereParamList.subList(0, whereParamList.size()-2).toArray());
		int recordsTotal=0;
		double totalDataLength=0;
		
		
		if(countRow.get("count_num")!=null){
			recordsTotal=new Integer(countRow.get("count_num").toString()).intValue();
		}
		if(countRow.get("data_length")!=null){
			totalDataLength=new Double(countRow.get("data_length").toString()).doubleValue();
		}
		
		//拼装返回的数据
		data.put("data", dataList.toArray());
		data.put("itemsCount", recordsTotal);
		data.put("totalSize", totalDataLength);
		
		
		return WebUtil.resultString(data,request);
	}
	
	
	
}
